﻿-- =============================================
-- Author:		<Author,XFL>
-- Create date: <Create Date,2012 02 01>
-- Description:	<Description, 
-- [人资管理]-[员工工资管理]-[工资生成及核算]
-- [财务管理]-[工资发放]-[发放工资]
-- 部门查询是 使用了部门编号查询 BMBH >
-- =============================================
CREATE PROCEDURE [dbo].[proc_Wage_Getlist]
	(
		@CompanyId int,
		@DepId int,
		@Month nvarchar(7),
		@EmpState int,
		@HRFlag int
	)
AS
Begin
	

			declare @bmbh_T  varchar(50)
			set @bmbh_T = (select bmbh  FROM department WHERE id=@DepId)		
			declare @indextable table(id int identity(1,1),nid int)
			insert into @indextable(nid) Select Id From department Where Left(bmbh,Len(@bmbh_T))=@bmbh_T;

		-- ====================================================
		--	添加 查询总共工资数据记录数[含在职数、离职数] 
		--	     查询通过核算工资记录数[含在职数、离职数] 
		--	XFL 20111230
		-- ====================================================
		-- 查询总共工资数据记录数
		declare @Record_count int
		set @Record_count= (select count(0) From Employee_Wage Where CompanyId=@CompanyId and (@DepId=0 Or DepartmentId in(select c.nid from @indextable c)) and Convert(nchar(7),FaFangMonth,120)=@Month )
		
		-- 在职数
		declare @Record_count_State_1 int
		set @Record_count_State_1 =(select count(0) From Employee_Wage Where CompanyId=@CompanyId and (@DepId=0 Or DepartmentId in(select c.nid from @indextable c)) and Convert(nchar(7),FaFangMonth,120)=@Month and StateId=1)

		-- 离职数 >> Record_count- Record_count_State_1

		-- 查询通过核算工资记录数
		declare @Record_count_HRFlag_2 int
		set @Record_count_HRFlag_2=(select count(0) From Employee_Wage Where CompanyId=@CompanyId and (@DepId=0 Or DepartmentId in(select c.nid from @indextable c)) and Convert(nchar(7),FaFangMonth,120)=@Month and HRFlag=2)

		-- 在职数
		declare @Record_count_HRFlag_2_State_1 int
		set @Record_count_HRFlag_2_State_1=(select count(0) From Employee_Wage Where CompanyId=@CompanyId and (@DepId=0 Or DepartmentId in(select c.nid from @indextable c)) and Convert(nchar(7),FaFangMonth,120)=@Month and HRFlag=2 and StateId=1)

		-- 离职数 >> Record_count_HRFlag_2 - Record_count_HRFlag_2_State_1

	;WITH list As(Select --ROW_NUMBER() OVER (ORDER BY DepartmentId,username) AS Row,
		Id
		,GId
		,UserName
		,EName
		,CompanyId
		,(Select CompanyName From Company Where Id=Employee_Wage.CompanyId) As CompanyName
		,DepartmentId
		,DepName
		,ZhiWu
		,GradeId
		,GradeName
		,IsNull(PostCategoryId,0) as PostCategoryId
		,PostCategoryName
		,PostId
		,PostName
		,ChuQinDays
		,BasicWage
		,PostWage
		,WorkWage
		,JxWage
		,ManQin
		,Allowance
		--,FixedAllowance as FAllowance
		-- 程序中使用的均为 FAllowance
		,QtAllowance
		,TiCheng
		,MTiCheng
		,YjBonus
		,LsBonus
		,ChuQinKouKuan
		,BxKouKuan
		,ChiDaoKouKuan
		,YJKouKuan
		,OtherKouKuan
		,HeJiWage
		,FaFangMonth
		,CreateName
		,CreateDate
		,HRFlag
		,ISNULL(HRFlagDate,'1900-01-01') As HRFlagDate
		,FinanceFlag
		,ISNULL(FinanceFlagDate,'1900-01-01') As FinanceFlagDate
		,Guid_str
		,isnull(XSTC,0) XSTC, isnull(XFTC,0) XFTC, isnull(BLTC,0) BLTC, isnull(FAllowance,0) FAllowance,
		 isnull(LSwance,0) LSwance, isnull(ZBwance,0) ZBwance, 
		 isnull(GJJKouKuan,0) GJJKouKuan, 
		 isnull(GRSDSKouKuan,0) GRSDSKouKuan, isnull(YFHeJi,0) YFHeJi, isnull(DFHeJi,0) DFHeJi, 
		 FinanceFlagDate_DF, isnull(FinanceFlag_DF,1) FinanceFlag_DF
		 ,StateId,WageCardNo,isnull(Deduction,0) as Deduction
		From Employee_Wage Where CompanyId=@CompanyId and (@DepId=0 Or DepartmentId in(select c.nid from @indextable c)) and Convert(nchar(7),FaFangMonth,120)=@Month and HRFlag=@HRFlag
		and (@EmpState=0 or StateId=@EmpState)
	)
	Select ROW_NUMBER() OVER (ORDER BY (select bmbh from Department where Id=list.DepartmentId),username) row,*
	,@Record_count as Record_count
	,@Record_count_State_1 as Record_count_State_1
	,@Record_count_HRFlag_2 as Record_count_HRFlag_2
	,@Record_count_HRFlag_2_State_1 as Record_count_HRFlag_2_State_1
	,convert(varchar(10),isnull((select ruzhidate from Employee where list.UserName=employee.username ) ,''),120) as ruzhidate
,(select sum(YFHeJi) from list ) as YFHeJi_zj
From list --order by UserName
End
	RETURN
